# -*- coding: utf-8 -*-
# @作者  : 张腾飞 骆冠楠
# @时间    : 2021/12/13
# @说明:所有数据如果没有特定说明 默认都是字典类型
import datetime
import sys
import pymssql
import Database
import mainWindow
from PyQt5.QtWidgets import QApplication, QMainWindow, QMessageBox, QGraphicsDropShadowEffect, QTableWidget, QHeaderView
from PyQt5.QtCore import Qt
from PyQt5 import QtCore
from sql import SQL
from core import EmployeeManagement, TableOutput, Statistic
import threading



class LoginWindow(QMainWindow, Database.Ui_login):
    def __init__(self):
        super(LoginWindow, self).__init__()
        # 将UI文件翻译成python代码
        self.setupUi(self)
        self.connect = None
        self.mainWindow = None
        self.mPos = None
        self.loginButton.clicked.connect(self.login)
        self.closeButton.clicked.connect(self.close)

        # 去掉QT自带边框 背景透明（最外边物体透明）
        self.setWindowFlags(self.windowFlags() | Qt.FramelessWindowHint)
        self.setAttribute(Qt.WA_TranslucentBackground, True)

        # 添加边框阴影
        effect = QGraphicsDropShadowEffect(self)
        effect.setBlurRadius(12)
        effect.setOffset(0, 0)
        effect.setColor(Qt.gray)
        self.setGraphicsEffect(effect)

    # 鼠标移动

    # 鼠标点击事件
    def mousePressEvent(self, event):
        if event.button() == Qt.LeftButton:
            self.mPos = event.pos()
        event.accept()

    # 鼠标弹起事件
    def mouseReleaseEvent(self, event):
        self.mPos = None
        event.accept()

    # 鼠标拖动事件
    def mouseMoveEvent(self, event):
        if event.buttons() == Qt.LeftButton and self.mPos:
            self.move(self.mapToGlobal(event.pos() - self.mPos))
        event.accept()

    def login(self):
        username = self.usernameEdit.text()
        password = self.passwd.text()
        self.connectSQLSERVER(username, password)

    def connectSQLSERVER(self, username, password):
        try:
            self.connect = pymssql.connect('(local)', username, password, 'employee', as_dict=True, charset='UTF-8')
            QMessageBox.about(self,
                              '提示信息',
                              '登录成功')
        except BaseException:
            QMessageBox.critical(self,
                                 '错误信息',
                                 '账号或密码错误')
        else:
            self.jumpToNewWindow(username)

    def jumpToNewWindow(self, username):
        self.mainWindow = MainWindow(self.connect, username)
        self.mainWindow.show()
        self.close()


class MainWindow(QMainWindow, mainWindow.Ui_MainWindow):
    """
    数据格式:
        self.updated_data={employee_id: {'changed_data_key': 'changed_data_value}}
    """
    def __init__(self, connect, username):
        super(MainWindow, self).__init__()
        self.setupUi(self)
        self.connect = connect
        self.mPos = None
        # 映射tableWidget的列名与数据库的列名
        self.mapping_rule = {'员工编号': 'employee_id', '员工姓名': 'employee_name', '员工电话': 'employee_phone',
                             '婚姻状况': 'employee_marriage_status', '工作部门': 'department_id', '员工年龄': 'employee_age',
                             '工作职位': 'post_id', '入职日期': 'employee_hiredate', '员工学历': 'academy_id',
                             '员工状态': 'status_id', '现役员工': '(status_id=1 OR status_id=2)',
                             '历史员工': '(status_id=3 OR status_id=4 OR status_id=5)',
                             }
        self.sql = SQL(self.connect, self.mapping_rule)
        self.manager = EmployeeManagement(self.sql, mainWindow.Ui_MainWindow)
        self.updated_data = {}

        self.username.setText(username)
        self.exitButton.clicked.connect(self.close)
        self.searchBtn.clicked.connect(self.serachBtnClicked)
        self.saveButton.clicked.connect(self.registerBtnClicked)
        self.modifySearchBtn.clicked.connect(self.modifySearchBtnClicked)
        self.modifyUpdateBtn.clicked.connect(self.updateBtnClicked)
        self.modifyTable.cellChanged.connect(self.contentChanged)
        self.transferSearchBtn.clicked.connect(self.transferSearchBtnClicked)
        self.transferUpdateBtn.clicked.connect(self.updateBtnClicked)
        self.transferTable.cellChanged.connect(self.contentChanged)
        self.statisticsButton.clicked.connect(self.statisticBtnClicked)
        self.searchSaveFileBtn.clicked.connect(lambda: self.exportBtnClicked(self.selectTable_2))
        self.exportButton.clicked.connect(lambda: self.exportBtnClicked(self.selectTable_3))
        # 切换界面
        self.employeeToolButton.clicked.connect(lambda: self.stackedWidget.setCurrentIndex(0))
        self.searchToolButton.clicked.connect(lambda: self.stackedWidget.setCurrentIndex(1))
        self.statisticsToolButton.clicked.connect(lambda: self.stackedWidget.setCurrentIndex(2))

        # 背景透明（最外边物体透明）
        self.setWindowFlags(self.windowFlags() | Qt.FramelessWindowHint)
        self.setAttribute(Qt.WA_TranslucentBackground, True)

        # 添加边框阴影
        effect = QGraphicsDropShadowEffect(self)
        effect.setBlurRadius(10)
        effect.setOffset(0, 0)
        effect.setColor(Qt.red)
        self.setGraphicsEffect(effect)
        self.beautifyTable()

    def beautifyTable(self):
        # 修改表
        self.centralwidget.setStyleSheet("""border-radius: 35px""")
        self.modifyTable.horizontalHeader().setSectionResizeMode(QHeaderView.Fixed)
        self.modifyTable.horizontalHeader().setMinimumWidth(150)
        self.modifyTable.horizontalHeader().setStyleSheet("""
        QHeaderView::section{
            background-color: rgb(40,118,102);
            border: 2px solid #000000;
            color: #ffffff;
        }
        """)
        self.modifyTable.verticalHeader().setStyleSheet("""
        QHeaderView::section{
            background-color: rgb(212,192,185);
        }
        """)
        # 调动表
        self.transferTable.horizontalHeader().setSectionResizeMode(QHeaderView.Fixed)
        self.transferTable.horizontalHeader().setMinimumWidth(150)
        self.transferTable.horizontalHeader().setStyleSheet("""
           QHeaderView::section{
            background-color: rgb(40,118,102);
            border: 2px solid #000000;
            color: #ffffff;
        }
        """)
        self.transferTable.verticalHeader().setStyleSheet("""
         QHeaderView::section{
            background-color: rgb(212,192,185);
        }
        """)
        # 查询表
        # 根据文字内容长度，自动调整单元格大小
        self.selectTable_2.horizontalHeader().setSectionResizeMode(QHeaderView.Fixed)
        self.selectTable_2.horizontalHeader().setMinimumWidth(200)
        self.selectTable_2.horizontalHeader().setStyleSheet("""
           QHeaderView::section{
            background-color: rgb(40,118,102);
            border: 2px solid #000000;
            color: #ffffff;
        }
        """)
        self.selectTable_2.verticalHeader().setStyleSheet("""
          QHeaderView::section{
            background-color: rgb(212,192,185);
        }
        """)

        # 统计表
        self.selectTable_3.horizontalHeader().setSectionResizeMode(QHeaderView.Fixed)
        self.selectTable_3.horizontalHeader().setMinimumWidth(150)
        self.selectTable_3.horizontalHeader().setStyleSheet("""
          QHeaderView::section{
            background-color: rgb(40,118,102);
            border: 2px solid #000000;
            color: #ffffff;
        }
        """)

        self.selectTable_3.verticalHeader().setStyleSheet("""
         QHeaderView::section{
            background-color: rgb(212,192,185);
        }
        """)

    # 鼠标移动

    # 鼠标点击事件
    def mousePressEvent(self, event):
        if event.button() == Qt.LeftButton:
            self.mPos = event.pos()
        event.accept()

    # 鼠标弹起事件

    def mouseReleaseEvent(self, event):
        self.mPos = None
        event.accept()

        # 鼠标拖动事件

    def mouseMoveEvent(self, event):
        if event.buttons() == Qt.LeftButton and self.mPos:
            self.move(self.mapToGlobal(event.pos() - self.mPos))
        event.accept()

    def serachBtnClicked(self):
        table_widget = TableOutput(self.selectTable_2)
        table_widget.removeAllCell()
        condition1_name = '现役员工'
        condition1_value = self.searchCondition1.currentText()
        condition2_name = self.searchCondition2.currentText()
        condition2_value = self.searchCondition3.text()
        key = [condition1_name, condition2_name]
        value = [condition1_value, condition2_value]
        condition = self.convertListToDict(key, value)
        condition = self.manager.mappingWindowToDatabase(condition, self.mapping_rule)

        data = self.manager.searchEmployee({'employee': True, 'department': True, 'post': True, 'academy': True},
                                           None, condition)
        # 窗口column与数据库对应
        table_widget.setMappingList(self.mapping_rule)
        table_widget.appendTableWidgetItem(data)

    def registerBtnClicked(self):
        employee_management = self.manager
        table_employee = 'employee'
        table_salary = 'salary'
        table_academy = 'academy'

        # 薪资
        salary_column = {'1': 'TOP 1 salary_id'}
        order = 'ORDER BY salary_id*1 DESC'
        last_salary_id = self.sql.select(table=table_salary, column=salary_column,
                                         order_condition=order)[0]
        salary_basic = self.wagesLineEdit.text()
        salary_id = str(int(last_salary_id['salary_id'].split()[0]) + 1)
        salary = {'salary_basic': salary_basic, 'salary_allowance': 0,
                  'salary_overtime': 0, 'salary_id': salary_id}

        # 学历
        academy_column = {'1': 'TOP 1 academy_id'}
        order = 'ORDER BY academy_id*1 DESC'
        last_academy_id = self.sql.select(table=table_academy, column=academy_column,
                                          order_condition=order)[0]
        academy_id = str(int(last_academy_id['academy_id'].split()[0]) + 1)
        academy_diploma = self.DegreeChoice.currentText()
        academy_graduate_school = self.CollegesLineEdit.text()
        academy_gpa = self.GPALineEdit.text()
        academy = {'academy_diploma': academy_diploma, 'academy_id': academy_id,
                   'academy_graduate_school': academy_graduate_school, 'academy_gpa': academy_gpa
                   }

        # 员工 -->改善代码 重复代码封装为一个功能
        employee_column = {'1': 'TOP 1 employee_id'}
        order = 'ORDER BY employee_id*1 DESC'
        last_employee_id = self.sql.select(table=table_employee, column=employee_column,
                                           order_condition=order)[0]
        employee_id = str(int(last_employee_id['employee_id'].split()[0]) + 1)
        employee_name = self.NameLineEdit.text()
        employee_sex = self.sexCombobox.currentText()
        employee_age = self.ageSpinBox.text()
        employee_hiredate = self.HireDateEdit.text()
        employee_leftdate = self.ContractDateEdit.text()
        employee_phone = self.numberLineEdit.text()
        employee_marriage_status = self.MarriageChoice.currentText()
        employee_idcard = self.identityLineEdit.text()
        status_id = self.StatusChoice.currentText()[:1]
        department_id = self.departmentCombobox.currentText()[:2]
        post_id = self.postsComboBox.currentText()[:1]
        employee_info = {'employee_name': employee_name, 'employee_id': employee_id,
                         'employee_sex': employee_sex, 'employee_age': employee_age,
                         'employee_hiredate': employee_hiredate, 'employee_phone': employee_phone,
                         'employee_marriage_status': employee_marriage_status, 'department_id': department_id,
                         'post_id': post_id, 'academy_id': academy_id,
                         'employee_leftdate': employee_leftdate, 'status_id': status_id,
                         'employee_idcard': employee_idcard, 'salary_id': salary_id}

        employee_management.registerEmployee(employee_info, salary, academy)
        # 清空内容
        self.NameLineEdit.setText("")
        self.ageSpinBox.cleanText()
        self.identityLineEdit.setText("")
        self.numberLineEdit.setText("")
        self.GPALineEdit.setText("")
        self.CollegesLineEdit.setText("")
        self.wagesLineEdit.setText("")

    def transferSearchBtnClicked(self):
        table_widget = TableOutput(self.transferTable)

        # 避免插入数据时cell的属性改变导致信号被触发
        self.blockSignals(True)
        table_widget.removeAllCell()
        self.modifyUpdateBtn.setEnabled(True)
        search_unit = {'employee': True, 'status': True}
        condition1 = [self.transferChoice.currentText(), ]
        condition1_value = [self.transferInput.text(), ]
        condition = self.convertListToDict(condition1, condition1_value)
        condition = self.manager.mappingWindowToDatabase(condition, self.mapping_rule)
        data = self.manager.searchEmployee(search_unit, None, condition)

        table_widget.setMappingList(self.mapping_rule)
        table_widget.appendTableWidgetItem(data)
        self.blockSignals(False)

    def modifySearchBtnClicked(self):
        table_widget = TableOutput(self.modifyTable)

        # 避免插入数据时cell的属性改变导致信号被触发
        self.modifyTable.blockSignals(True)
        table_widget.removeAllCell()
        self.modifyUpdateBtn.setEnabled(True)
        search_unit = {'employee': True, 'department': True}
        condition1 = [self.modifyChoice.currentText(), ]
        condition1_value = [self.modifyInput.text(), ]
        condition = self.convertListToDict(condition1, condition1_value)
        condition = self.manager.mappingWindowToDatabase(condition, self.mapping_rule)
        data = self.manager.searchEmployee(search_unit, None, condition)

        table_widget.setMappingList(self.mapping_rule)
        table_widget.appendTableWidgetItem(data)
        self.modifyTable.blockSignals(False)

    def updateBtnClicked(self):
        condition = {}
        for key in self.updated_data:
            condition['employee_id'] = key
            value = self.updated_data[key]
            self.manager.updateEmployeeInfo('employee', value, condition)
        self.updated_data = {}

    def contentChanged(self, row, column):
        # 阻塞信号,防止错误
        self.modifyTable.blockSignals(True)
        sender_table = self.sender()

        table_widget = TableOutput(sender_table)
        table_widget.getRowCount()
        if table_widget.row_count == 0:
            return
        table_widget.setMappingList(self.mapping_rule)
        mapping_list = table_widget.mapping_list
        employee_id = sender_table.item(row, 0).text()
        changed_data_key = mapping_list[column]
        if sender_table.horizontalHeaderItem(column).text() in ['工作部门', '员工状态']:
            changed_data_value = sender_table.item(row, column).text().split()[0]
        else:
            changed_data_value = sender_table.item(row, column).text()
        if employee_id in self.updated_data.keys():
            dit = self.updated_data[employee_id]
            new_data = {changed_data_key: changed_data_value}
            dit.update(new_data)
            self.updated_data[employee_id] = dit
        else:
            self.updated_data[employee_id] = {changed_data_key: changed_data_value}
        self.modifyTable.blockSignals(False)

    def statisticBtnClicked(self):
        text1 = self.statistics_1Choice.currentText()
        table_widget = self.selectTable_3
        text = self.statistics_2Choice.currentText()
        condition1 = {text1: self.mapping_rule[text1]}
        if self.mapping_rule[text] == 'academy_id':
            column = {self.mapping_rule[text]: 'DISTINCT ' +'academy_graduate_school'}
        else:
            column = {self.mapping_rule[text]: 'DISTINCT ' + 'employee.' + self.mapping_rule[text]}
        table = {'employee': True, 'post': True, 'academy': True}
        condition_convert = ""
        condition2 = {text1: self.mapping_rule[text1]}
        # 名称
        xlabel_name = ""
        ylabel_name = "人数"
        # 数据部分
        x_data = self.manager.searchEmployee(table, column, condition1)
        x_y_data = []
        diagram_data = {}
        for data in x_data:
            for x in data:
                condition_value = data[x]
                if isinstance(condition_value, datetime.date):
                    # 将日期格式转为字符串格式
                    condition_value = condition_value.strftime('%Y-%m-%d')
                #xlabel_name = [k for k, v in self.mapping_rule.items() if v == x][0]   # 映射数据库中所对应的名字
                if self.mapping_rule[text] == 'academy_id':
                    column = {self.mapping_rule[text]: f'COUNT({x}) AS "{condition_value}"'}
                else:
                    column = {self.mapping_rule[text]: f'COUNT(employee.{x}) AS "{condition_value}"'}
                #转换字符串为SQL接受的条件格式
                if self.mapping_rule[text] == 'post_id':
                    condition_convert = condition_value.split()[1]
                else:
                    condition_convert = condition_value

                if self.mapping_rule[text] == 'academy_id':
                    condition2.update({'academy_graduate_school': condition_convert})
                # 改为post_name是为了不与多表查询条件冲突
                elif self.mapping_rule[text] == 'post_id':
                    condition2.update({'post_name': condition_convert})
                else:
                    condition2.update({'employee.' + self.mapping_rule[text]: condition_convert})
                x_y_data.append(self.manager.searchEmployee(table, column, condition2)[0])
        statistic = Statistic(True)
        # 加入多线程解决页面刷新问题 matplotlib画图不太支持多线程
        thread1 = threading.Thread(target=self.threadProc, args=(x_y_data, table_widget, diagram_data, statistic))
        thread1.start()

        statistic.drawFigure(xlabel_name, ylabel_name, diagram_data)

    def threadProc(self, x_y_data, table_widget, diagram_data, statistic):
        for dit in x_y_data:
            diagram_data.update(dit)
            statistic.analyseAndPrintData(table_widget, dit)

    def exportBtnClicked(self, table_widget):
        table = TableOutput(table_widget)
        file_name = table.getFileName()
        if file_name:
            table.exportAsExcel("导出结果", file_name)

    def convertListToDict(self, key, value):
        # 接收两个列表参数
        dit = zip(key, value)
        return dict(dit)


if __name__ == '__main__':
    QtCore.QCoreApplication.setAttribute(QtCore.Qt.AA_EnableHighDpiScaling)
    app = QApplication(sys.argv)
    loginWindow = LoginWindow()
    loginWindow.show()
    sys.exit(app.exec_())
